package com.logic.common.util;


import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;

@ApiModel(description = "QueryUtil Object")
public class QueryUtil {

  @ApiModelProperty(value = "paging tool", position = 1, required = true)
  private PagingTool pagingTool;
  @ApiModelProperty(value = "query criterial", position = 2)
  private List<QueryCriteria> queryCriterias;
  @ApiModelProperty(value = "order by", position = 3)
  private List<QueryOrderBy> queryOrderBies;
  @ApiModelProperty(value = "params",  position = 4)
  private Map<String, Object> params = new HashMap<String, Object>();

  private final String NOT_IN = "not in";

  public PagingTool getPagingTool() {
    return pagingTool;
  }

  public void setPagingTool(PagingTool pagingTool) {
    this.pagingTool = pagingTool;
  }

  public List<QueryCriteria> getQueryCriterias() {
    return queryCriterias;
  }

  public void setQueryCriterias(List<QueryCriteria> queryCriterias) {
    this.queryCriterias = queryCriterias;
  }

  public List<QueryOrderBy> getQueryOrderBies() {
    return queryOrderBies;
  }

  public void setQueryOrderBies(List<QueryOrderBy> queryOrderBies) {
    this.queryOrderBies = queryOrderBies;
  }

  public Map<String, Object> getParams() {
    return params;
  }

  public void setParams(Map<String, Object> params) {
    this.params = params;
  }

  /**
   * The sample return string is like: col1 = '1' and col2 > 2 and col2 <=5 or col3 >='3' and col4 in ('B41', 'B42')
   * order by col1 ASC
   *
   * @return
   */
  @ApiModelProperty(hidden = true)
  public String getFullWhereClause(String databaseId) {
    StringBuffer sb = new StringBuffer();

    List<QueryCriteria> queryCriterias = getQueryCriterias();
    int size;

    if (queryCriterias != null) {

      size = getQueryCriterias().size();

      // Loop queryCriteria get first section of fullQueryCriteria
      for (int i = 0; i < size; i++) {
        QueryCriteria q = getQueryCriterias().get(i);

        // BEGIN Updated by OMER (2015-12-07)
        // default a group, i=0 add "(",  i=size-1 add ")"
        if (i == 0) { //&& q.getConnection().indexOf("(", 0) > 0
			sb.append(" ( ");
		}

        if (i > 0) {
        	sb.append(" ");
        }

        if (i != 0){
          sb.append(q.getConnection());
        }
        sb.append(" ").append("UPPER("+q.getKey()+")").append(" ").append(q.getCondition());
        if(databaseId != null && databaseId.equals("mssql")){
        	if("like".equals(q.getCondition())){
        		sb.append(" N");
        	}
        	else{
        		sb.append(" ");
        	}
        }else{
        	sb.append(" ");
        }
        // END Updated by OMER (2015-12-07)

        if (QueryCriteria.LIKE.equals(q.getCondition()) && q.getIsValueADigital().booleanValue() == false) {
          sb.append("'");
        }

        if ((QueryCriteria.EQUAL.equals(q.getCondition()) ||
                QueryCriteria.MORE_THAN.equals(q.getCondition()) ||
                QueryCriteria.LESS_THAN.equals(q.getCondition()) ||
                QueryCriteria.MORE_THAN_AND_EQUAL.equals(q.getCondition()) ||
                QueryCriteria.LESS_THAN_AND_EQUAL.equals(q.getCondition()) ||
                QueryCriteria.NOT_EQUAL.equals(q.getCondition())) &&
                q.getIsValueADigital().booleanValue() == false) {
          sb.append("'");
        }

        if (QueryCriteria.LIKE.equals(q.getCondition())) {
          sb.append("%");
        }

        if (QueryCriteria.IN.equals(q.getCondition()) || NOT_IN.equals(q.getCondition())) {
          sb.append("(");
        }


        if ((QueryCriteria.IN.equals(q.getCondition())
        		|| NOT_IN.equals(q.getCondition()))
        		&& q.getIsValueADigital().booleanValue() == false) {
          String[] splurt = q.getValue().toUpperCase().split(",");
          boolean first = true;
          for (String each : splurt){
            if (q.getIsValueADigital().booleanValue() == false) {
              if (!first){
                sb.append(",");
              }else{
                first = false;
              }
              sb.append("'");
              sb.append(each.trim());
              sb.append("'");
            }else{
              if (!first){
                sb.append(",");
              }else{
                first = false;
              }
              sb.append(each.trim());
            }
          }
        }

        if (!(QueryCriteria.IN.equals(q.getCondition())
        		|| NOT_IN.equals(q.getCondition()))){
          if (isDate(q.getValue().toUpperCase())){
            sb.append("TO_DATE ( SUBSTR ( '")
                    .append(q.getValue().toUpperCase())
                    .append("', 1, 19), 'YYYY-MM-DD\"T\"HH24:MI:SS')");
          }else{
            sb.append(q.getValue().toUpperCase());
          }

        }


        if (QueryCriteria.LIKE.equals(q.getCondition())) {
          sb.append("%");
        }

        if (QueryCriteria.IN.equals(q.getCondition())
        		|| NOT_IN.equals(q.getCondition())) {
          sb.append(")");
        }

        if (QueryCriteria.LIKE.equals(q.getCondition()) && q.getIsValueADigital().booleanValue() == false) {
          sb.append("'");
        }

        if ((QueryCriteria.EQUAL.equals(q.getCondition()) ||
                QueryCriteria.MORE_THAN.equals(q.getCondition()) ||
                QueryCriteria.LESS_THAN.equals(q.getCondition()) ||
                QueryCriteria.MORE_THAN_AND_EQUAL.equals(q.getCondition()) ||
                QueryCriteria.LESS_THAN_AND_EQUAL.equals(q.getCondition()) ||
                QueryCriteria.NOT_EQUAL.equals(q.getCondition())) &&
                q.getIsValueADigital().booleanValue() == false) {
          sb.append("'");
        }

        if(i == size - 1) {
        	sb.append(" ) ");
        }

      }

    }

    if (sb.toString().isEmpty()){
      sb.append(" 1=1 ");
    }

    List<QueryOrderBy> queryOrderBies = getQueryOrderBies();
    if (queryOrderBies != null) {
      // Add order by clause
      size = getQueryOrderBies().size();
      if (size > 0) {
        sb.append(" ORDER BY");
      }

      for (int i = 0; i < size; i++) {
        QueryOrderBy q = queryOrderBies.get(i);

        if (i > 0) {
          sb.append(",");
        }

        sb.append(" ").append(q.getColumnName()).append(" ").append(q.getOrderType());
      }
    }

    // Please reference PageInterceptor for how to add fullWhereClause and page limit.



    return sb.toString();
  }

  @ApiModelProperty(hidden = true)
  public String getRootOrderClause(){
    StringBuffer sb = new StringBuffer();
    List<QueryOrderBy> queryOrderBies = getQueryOrderBies();
    if (queryOrderBies != null) {
      // Add order by clause
      int size = getQueryOrderBies().size();
      for (int i = 0; i < size; i++) {
        QueryOrderBy q = queryOrderBies.get(i);

        if (i > 0) {
          sb.append(",");
        }

        sb.append(" a.").append(q.getColumnName()).append(" ").append(q.getOrderType());
      }
    }

    return sb.toString();
  }

  private boolean isDate(String input){
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");

    try {
      sdf.parse(input);
    } catch (ParseException e) {
      return false;
    }

    return true;

  }

@Override
public String toString() {
	return "QueryUtil [pagingTool=" + pagingTool + ", queryCriterias=" + queryCriterias + ", queryOrderBies="
			+ queryOrderBies + ", params=" + params + "]";
}



}
